import os
import csv
from collections import Counter
import pyreadstat

from src.common.util.mysqlUtil import MySqlUtil
from setting import mysqlConfig

mysql = MySqlUtil(mysqlConfig)


class Sav2DB():
    def __init__(self):
        self.data = {}
        self.getNaire()
        self.getSchool()
        self.getQuestion()

    def getNaire(self):
        naireTuple = mysql.select(sql='select id,title from naire')
        naireDict = {}
        _naireDict = {}
        for list in naireTuple:
            naireDict[list[0]] = list[1]
            _naireDict[list[1]] = list[0]
        # print(naireDict)
        self.naireDict = naireDict
        self._naireDict = _naireDict

    def getSchool(self):
        schoolTuple = mysql.select(sql='select id,name from department')
        schoolDict = {}
        _schoolDict = {}
        for list in schoolTuple:
            schoolDict[list[0]] = list[1]
            _schoolDict[list[1]] = list[0]
        self.schoolDict = schoolDict
        self._schoolDict = _schoolDict

    def getQuestion(self):
        questionTuple = mysql.select(sql='select q_id,content from question')
        questionDict = {}
        _questionDict = {}
        for list in questionTuple:
            questionDict[list[0]] = list[1]
            _questionDict[list[1]] = list[0]
        self.questionDict = questionDict
        self._questionDict = _questionDict

    def analysisFilename(self, filename):
        temp = filename.split('-')
        n_name = temp[0]
        s_name = temp[1]
        n_id = self._naireDict[n_name]
        s_id = self._schoolDict[s_name]
        return n_id, s_id

    def getQuestionByNId(self, n_id):
        questionTuple = mysql.select(sql=f'select q_id,content from question where n_id={n_id}')
        print(questionTuple)
        questionDict = {}
        _questionDict = {}
        for list in questionTuple:
            questionDict[list[0]] = list[1]
            _questionDict[list[1]] = list[0]
        return questionDict, _questionDict

    def getSav(self):
        """
        处理全国数据
        :return:
        """
        path = './data/'
        filenameList = os.listdir(path)

        for filename in filenameList:
            if (('学习收获模块' in filename) or ('学习过程模块' in filename)):
                n_id, s_id = self.analysisFilename(filename)
                self.analysisNaire(n_id, path, filename)

        # self.saveJSon(self.data)
        self.saveCSV(self.data)
        # self.saveDb(self.data)
        # self.saveSQL(self.data)

    def readSav(self, n_name, q_idList):
        """
        计算全国数据
        :param n_name:
        :param q_idList:
        :return:
        """
        path = './data/'
        filenameList = os.listdir(path)
        allJson = []
        print(filenameList)
        for filename in filenameList:
            if n_name in filename:
                try:
                    savRes, meta = pyreadstat.read_sav(path + filename)
                    df = savRes.loc[:, q_idList]
                    allJson += eval(df.to_json(orient="records", force_ascii=False))
                    pass
                except Exception as e:
                    pass
            else:
                pass
        if (n_name == '学习收获模块'):
            print(allJson)
            self.getAverage(allJson)
        elif (n_name == '学习过程模块'):
            print(allJson)
            self.getCount(allJson)

    def analysisNaire(self, n_id, path, filename):
        try:
            if (n_id == 527):
                savRes, meta = pyreadstat.read_sav(path + filename)
                self.abilityToHarvest(savRes, meta)
            elif (n_id == 528):
                savRes, meta = pyreadstat.read_sav(path + filename)
                self.timeInvested(savRes, meta)
                self.typeOfLearningStyle(savRes, meta)
            else:
                pass
        except Exception as e:
            pass

    def abilityToHarvest(self, df, meta):
        """
        能力收获
        :return:
        """
        allAve = {'q6736': 4.47902402141104, 'q6735': 4.4884633617973755, 'q6746': 4.590411838481065,
                  'q6762': 4.533739791050329, 'q6747': 4.5858391891648616, 'q6761': 4.4440810595104505}
        oColumns = ['q6736', 'q6735', 'q6746', 'q6762', 'q6747', 'q6761']
        nColumns = self.oColumns2nColumns(oColumns, df, meta)
        df = df.loc[:, ['u_id'] + oColumns]
        jsonList = eval(df.to_json(orient="records", force_ascii=False))
        # print(jsonList)
        for dict in jsonList:  # dict为用户级
            # print(dict)
            u_id = ''
            msg = '通过大学的学习，您的各项能力都获得了一定的提升。您的'
            data = []
            rateDict = {'优秀，超过全国平均水平': [], '优秀，达到全国平均水平': [], '优秀，低于全国平均水平': [],
                        '较为良好，超过全国平均水平': [], '较为良好，达到全国平均水平': [], '较为良好，低于全国平均水平': [],
                        '还需要进一步重视和加强，超过全国平均水平': [], '还需要进一步重视和加强，达到全国平均水平': [], '还需要进一步重视和加强，低于全国平均水平': []}
            for key in dict:
                value = dict[key]

                if key == 'u_id':
                    u_id = value
                elif key == 'q6736':
                    msgKey, rate, avgRate = self.getRate(value, allAve['q6736'])
                    rateDict[msgKey].append('批判思维能力')
                    data.append({"name": "批判思维", "value": rate, "avg": avgRate})
                elif key == 'q6735':
                    msgKey, rate, avgRate = self.getRate(value, allAve['q6735'])
                    rateDict[msgKey].append('自学能力')
                    data.append({"name": "自学能力", "value": rate, "avg": avgRate})
                elif key == 'q6746':
                    msgKey, rate, avgRate = self.getRate(value, allAve['q6746'])
                    rateDict[msgKey].append('沟通能力')
                    data.append({"name": "沟通能力", "value": rate, "avg": avgRate})
                elif key == 'q6762':
                    msgKey, rate, avgRate = self.getRate(value, allAve['q6762'])
                    rateDict[msgKey].append('社交能力')
                    data.append({"name": "社交能力", "value": rate, "avg": avgRate})
                elif key == 'q6747':
                    msgKey, rate, avgRate = self.getRate(value, allAve['q6747'])
                    rateDict[msgKey].append('团队合作能力')
                    data.append({"name": "团队合作", "value": rate, "avg": avgRate})
                elif key == 'q6761':
                    msgKey, rate, avgRate = self.getRate(value, allAve['q6761'])
                    rateDict[msgKey].append('领导能力')
                    data.append({"name": "领导能力", "value": rate, "avg": avgRate})

            for rateKey in rateDict:
                rateValue = rateDict[rateKey]
                rate = ''

                if (len(rateValue) == 0):
                    continue
                elif len(rateValue) == 1:
                    rate = rateValue[0]
                else:
                    count = 0
                    for item in rateValue:
                        if (count == 0):
                            rate += item
                        elif (count == (len(rateValue) - 1)):
                            rate += f'和{item}'
                        else:
                            rate += f'、{item}'

                        count += 1

                rate += rateKey
                msg += rate + '。'

            # print(u_id, msg, data)
            # self.saveText(f'{u_id}\t{msg}\n')
            if (u_id not in self.data):
                self.data[u_id] = {'u_id': u_id, 'result': {}, 'year': '2021', 'model': '2021'}
            self.data[u_id]['result']['AbilityToHarvest'] = {'msg': msg, 'data': data}

    def timeInvested(self, df, meta):
        """
        时间投入
        :return:
        """
        allCount = {'q6827': {1.0: 79015, 2.0: 63764, 3.0: 14046, 4.0: 6646, 5.0: 2703, 6.0: 1951, 7.0: 5074},
                    'q6824': {1.0: 19405, 2.0: 88051, 3.0: 43107, 4.0: 15207, 5.0: 3806, 6.0: 3623}}
        oColumns = ['q6827', 'q6824']
        nColumns = self.oColumns2nColumns(oColumns, df, meta)
        # print(nColumns)
        df = df.loc[:, ['u_id'] + oColumns]
        jsonList = eval(df.to_json(orient="records", force_ascii=False))
        # print(jsonList)
        for dict in jsonList:  # dict为用户级
            # print(dict)
            u_id = ''
            msg = '您'
            data = []

            for key in dict:
                value = dict[key]

                if key == 'u_id':
                    u_id = value
                elif key == 'q6827':
                    timeDict = allCount['q6827']
                    msg += f'平均每天花在英语学习上的时间有{self.getTimeI(value)}小时, 超过了全国{self.getRatio(value, timeDict)}%的学生；'
                    data.append({'name': '英语学习', 'value': self.getRatio(value, timeDict)})
                elif key == 'q6824':
                    timeDict = allCount['q6824']
                    msg += f'平均每天课后花在专业学习上的时间有{self.getTimeI(value)}小时，超过了全国{self.getRatio(value, timeDict)}%的学生。'
                    data.append({'name': '专业学习', 'value': self.getRatio(value, timeDict)})

            # print(u_id, msg)
            # self.saveText(f'{u_id}\t{msg}\n')
            if (u_id not in self.data):
                self.data[u_id] = {'u_id': u_id, 'result': {}, 'year': '2021', 'model': '2021'}
            self.data[u_id]['result']['TimeInvested'] = {'msg': msg, 'data': data}

    def typeOfLearningStyle(self, df, meta):
        """
        学习方式类型
        :return:
        """
        oColumns = ['q6772', 'q6773', 'q6774', 'q6775', 'q6780', 'q6781', 'q6782', 'q6783', 'q6784',
                    'q6776', 'q6777', 'q6778', 'q6779', 'q6791', 'q6792', 'q6793', 'q6794', 'q6795', 'q6796']
        nColumns = self.oColumns2nColumns(oColumns, df, meta)
        # print(nColumns)
        df = df.loc[:, ['u_id'] + oColumns]
        jsonList = eval(df.to_json(orient="records", force_ascii=False))
        # print(jsonList)
        for dict in jsonList:  # dict为用户级
            # print(dict)
            u_id = ''
            msg = '你'
            data = []

            at = 0
            pa = 0

            for key in dict:
                value = dict[key]

                if key == 'u_id':
                    u_id = value
                elif key in ['q6772', 'q6773', 'q6774', 'q6775', 'q6780', 'q6781', 'q6782', 'q6783', 'q6784']:
                    at += value
                elif key in ['q6776', 'q6777', 'q6778', 'q6779', 'q6791', 'q6792', 'q6793', 'q6794', 'q6795', 'q6796']:
                    pa += value

            atAvg = at / 9
            paAvg = pa / 10

            if atAvg > paAvg:
                msg = '您是主动思考型的学习者，比起死记硬背，您更喜欢联想和思考，更注重通过知识的学习来形成自己的观点或看法。'
                type = 1
            else:
                msg = '您是被动接受型的学习者，您更关注文本知识点的获取。死板的考试难不住您，但尝试多些发散思考也许会有惊喜。'
                type = 2
            data = [
                {
                    'name': '主动思考',
                    'value': '%.2f' % (atAvg / (atAvg + paAvg))
                }, {
                    'name': '被动接受',
                    'value': '%.2f' % (1 - (atAvg / (atAvg + paAvg)))
                }
            ]
            # print(u_id, msg, f'主动思考百分比：{atAvg / (atAvg + paAvg) * 100}%')
            # self.saveText(f'{u_id}\t{msg}\n')
            if (u_id not in self.data):
                self.data[u_id] = {'u_id': u_id, 'result': {}, 'year': '2021', 'model': '2021'}
            self.data[u_id]['result']['TypeOfLearningStyle'] = {'msg': msg, 'data': data, 'type': type}

    def getRate(self, record, avgDict):
        if (record >= 5.0):
            rate = '优秀'
        elif (record >= 3.0):
            rate = '较为良好'
        else:
            rate = '还需要进一步重视和加强'

        if (avgDict >= 4.5):
            avgrate = '优秀'
        elif (avgDict >= 2.5):
            avgrate = '较为良好'
        else:
            avgrate = '还需要进一步重视和加强'

        if rate == avgrate:
            msg = rate + '，达到全国平均水平'
        else:
            if record > avgDict:
                msg = rate + '，超过全国平均水平'
            else:
                msg = rate + '，低于全国平均水平'

        return msg, rate, avgrate

    def getTimeI(self, timeI):
        if (timeI == 1.0):
            return '少于1'
        elif (timeI == 2.0):
            return '1-3'
        elif (timeI == 3.0):
            return '4-6'
        elif (timeI == 4.0):
            return '7-9'
        elif (timeI == 5.0):
            return '10-12'
        else:
            return '12以上'

    def getRatio(self, timeI, timeDict):
        all = (timeDict[1.0] + timeDict[2.0] + timeDict[3.0] + timeDict[4.0] + timeDict[5.0] + timeDict[6.0])
        if (timeI == 1.0):
            ratio = timeDict[1.0] / all
            return '%.2f' % (ratio * 100)
        elif (timeI == 2.0):
            ratio = (timeDict[1.0] + timeDict[2.0]) / all
            return '%.2f' % (ratio * 100)
        elif (timeI == 3.0):
            ratio = (timeDict[1.0] + timeDict[2.0] + timeDict[3.0]) / all
            return '%.2f' % (ratio * 100)
        elif (timeI == 4.0):
            ratio = (timeDict[1.0] + timeDict[2.0] + timeDict[3.0] + timeDict[4.0]) / all
            return '%.2f' % (ratio * 100)
        elif (timeI == 5.0):
            ratio = (all - timeDict[6.0]) / all
            return '%.2f' % (ratio * 100)
        else:
            return '100'

    def oColumns2nColumns(self, oColumns, df, meta):
        q_idList = []
        for q_name in meta.column_labels:
            if (q_name != '用户列'):
                q_name = q_name.replace(r'\/', '/')
                q_idList.append(self._questionDict[q_name])
            else:
                q_idList.append('u_id')

        nColumns = []
        count = 0
        for oColumn in df.columns:
            if (oColumn in oColumns):
                nColumns.append(q_idList[count])
            count += 1

        return nColumns

    def getAverage(self, data):
        length = len(data)
        allDict = {'q6736': 0.0, 'q6735': 0.0, "q6746": 0.0, "q6762": 0.0, "q6747": 0.0, "q6741": 0.0}
        for item in data:
            allDict = dict(Counter(allDict) + Counter(item))

        avgDict = {}
        for key in allDict:
            value = allDict[key]

            avgDict[key] = value / length
        print(avgDict)

    def getCount(self, data):
        countDict = {'q6827':{1.0:0, 2.0:0, 3.0: 0, 4.0:0, 5.0: 0, 6.0:0, 7.0: 0}, 'q6824':{1.0:0, 2.0:0, 3.0: 0, 4.0:0, 5.0: 0, 6.0:0}}
        for item in data:
            for key in item:
                value = item[key]

                countDict[key][value] += 1

        print(countDict)

    def saveDb(self, data):
        for u_id in data:
            item = data[u_id]
            item['result'] = str(item['result']).replace("'", '"')

            keys = str(tuple(item.keys())).replace("'", "")
            values = str(tuple(item.values()))
            sql = f"insert into analysis {keys} values{values}"
            # print(sql)
            if mysql.insert(sql=sql) == 'success':
                pass
                # print('成功')
            else:
                pass
                # print('失败')

    def saveSQL(self, data):
        with open('数据.sql', 'w+', encoding='utf8') as fp:
            for u_id in data:
                item = data[u_id]
                item['result'] = str(item['result']).replace("'", '"')

                keys = str(tuple(item.keys())).replace("'", "")
                values = str(tuple(item.values()))
                sql = f"insert into analysis {keys} values{values}\n"
                fp.write(sql)

    def saveText(self, data):
        with open('data.txt', 'a+', encoding='utf8') as fp:
            fp.write(data)

    def saveJSon(self, data):
        data = str(list(data.values())).replace("'", '"')
        with open('数据.json', 'w+', encoding='utf8') as fp:
            fp.write(data)

    def saveCSV(self, data):
        with open('数据.csv', 'w+', encoding='utf-8', newline='' "") as fp:
            csv_writer = csv.writer(fp)
            csv_writer.writerow(['u_id', 'result', 'year', 'model'])

            for u_id in data:
                item = data[u_id]
                item['result'] = str(item['result']).replace("'", '"')

                values = list(item.values())
                csv_writer.writerow(values)


if __name__ == '__main__':
    import time
    sd = Sav2DB()
    # print(int(time.time()))
    sd.getSav()
    # print(int(time.time()))
    # sd.readSav('学习收获模块', ['q6736', 'q6735', 'q6746', 'q6762', 'q6747', 'q6761'])
    # sd.readSav('学习过程模块', ['q6827', 'q6824'])
